package com.citywy.jdbctemplate;

import com.citywy.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * jdbctemplate的CRUD
 */
public class JdbcTemplate3 {
    public static void main(String[] args) {
        //1.获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        //2.获取对象
        JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
        //3.执行操作
        //3.1 保存
//        jt.update("insert into account(name,money) value('fff',1000)");

        //3.2 更新
//        jt.update("update account set name=?,money=? where id=12","test",7000);

        //3.3 删除
//        jt.update("delete from account where id=?",12);

        //3.4.1 查询所有
//        List<Account> accountList = jt.query("select * from account where money>?",new AccountRowMapper(),100d);

        //3.4.2 使用spring自带的BeanPropertyRowMapper 将一个对象封装到集合里，不用我们自己写下面的AccountRowMapper
        /*List<Account> accountList = jt.query("select * from account where money>?",new BeanPropertyRowMapper<Account>(Account.class),100d);
        for(Account account:accountList){
            System.out.println(account);
        }*/

        //3.5 查询一个
        /*List<Account> accounts = jt.query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),1);
        if(!accounts.isEmpty()){
            System.out.println(accounts.get(0));
        }*/

        //3.6 查询返回一行一列(使用聚合函数...count,但不加group by子句)
        int count= jt.queryForObject("select count(*) from account where id>?",Integer.class,2);
        System.out.println(count);
    }
}

/**
 * 定义Account的封装策略，然后由spring把每个Account加到集合中
 */
class AccountRowMapper implements RowMapper<Account>{
    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();
        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getDouble("money"));
        return account;
    }
}